import shutil
import os
from flask import session
from sortingdataprocess import *

# 设置表格保留6位小数
# pd.set_option('display.float_format', lambda x: '%.6f' % x)


def last_coef(form3_infos, machine_name, machine_label, cnxn, conn, engine, logger):
    flag = 0
    lotID3 = None
    coef_data = form3_infos['coef_data']
    # coef_data = trans(coef_data)
    # 数据库存储校准NG记录:NG(状态)、NG步骤、NG原因，其他同OK记录，除时间外全为空
    NG_dict = {'MoID': form3_infos['MoID'], 'Machine_Label': machine_label,
               'LotID': lotID3,
               'Status': 0}
    # 创建游标
    cursor = cnxn.cursor()
    cursor.fast_executemany = True
    # 创建游标
    cursor1 = conn.cursor()
    cursor1.fast_executemany = True
    cal_requirments = {1: 1, 2: 1, 3: 2, 4: 2, 5: 3, 6: 3, 7: 4, 8: 5, 9: 6, 10: 6}
    # 查询web相关session是否为None
    item_code = get_item_code(form3_infos['MoID'])
    # 创建上传与下载目录
    uploadsDir = os.path.join('uploads', 'caldata', str(datetime.now().year), str(datetime.now().month))
    if not os.path.exists(uploadsDir):
        os.makedirs(uploadsDir)

    # 确认lotID3，存在+1，不存在初始化为1
    cursor.execute(
        f"SELECT MAX(LOTID3) FROM MPvalidation_Info1 WHERE MOID='{form3_infos['MoID']}' "
        f"AND LotID2='{form3_infos['LotID2']}';")
    rows = cursor.fetchall()
    if rows[0][0] is None:
        lotID3 = 1
    else:
        lotID3 = rows[0][0] + 1
    # 添加LOTID2到列表
    form3_infos['LotID3'] = lotID3
    NG_dict['LotID'] = str(session['lotID2']) + '-' + str(lotID3)

    # 判断测试数据中是否存在有效数据dlm\dI\dU
    if (form3_infos['MachineID'] is not None) and (coef_data is not None):
        CF2_pars = get_cf(item_code, 2)
        CF3_pars = get_cf(item_code, 3)
        standpart_data = get_standpart_data(item_code)
        test_data, error = get_test_data(form3_infos['temp_path'], machine_name, logger)
        if (not CF2_pars.empty) and (not CF3_pars.empty) \
                and (not standpart_data.empty) and (not test_data.empty):
            compare_data = cal_coef(standpart_data, test_data)
            if not compare_data.empty:
                compare_data.drop(index='校准系数', inplace=True)
                compare_data['dlm'] = compare_data['dlm'].apply(lambda x: x - 1)
                del_result = compare_data.query('(abs(dx) > 0.0015)|(abs(dy) > 0.0015)'
                                                '|(abs(dRa) > 2)|(abs(dR9) > 2)|(abs(dlm)>0.03)')
                # print(f"计算最终\n{coef_data}")
                end_coef = cal_end_coef(coef_data, CF2_pars, CF3_pars)
                # print(f"验证差异\n{end_coef}")
                # 验证成功的判断
                if len(del_result) >= cal_requirments[len(compare_data)]:
                    logger.warn(f'{form3_infos["MachineID"]}号最终系数验证失败')
                    result = del_result.append(end_coef)
                    flash_result = result
                    flash_result = flash_result.to_json(orient='records', force_ascii=False)
                    NG_dict['NG_Site'] = "校准第三步:验证测试数据编号"
                    NG_dict['Reason'] = f"测试数据与标准件数据编号不对应"
                    return flash_result, lotID3, flag, NG_dict
                else:
                    flash_result = end_coef
                    # 保存数据文件
                    ext = os.path.splitext(form3_infos['temp_path'])[1]
                    upnew_filename = 'caldata-' + form3_infos['MoID'] + '-' \
                                     + str(form3_infos['LotID2']) + '-' + str(lotID3) + ext
                    form3_infos['UploadPath'] = os.path.join(uploadsDir, upnew_filename)
                    shutil.copy(form3_infos['temp_path'], form3_infos['UploadPath'])
                    # 删除字典coef_data,temp_path及临时文件
                    os.remove(form3_infos.pop('temp_path'))
                    form3_infos.pop('coef_data')

                    pars = ['MoID', 'StandardPartID', 'MachineID', 'LotID2', 'LotID3', 'ProcessID']
                    for i in pars:
                        test_data[i] = form3_infos[i]
                    # print(f"最终系数:{test_data}")
                    # 数据写入数据库
                    try:
                        test_data.to_sql('MPvalidation_Data1', engine, if_exists='append', index=None)
                    except Exception as e:
                        logger.error(f"{form3_infos['MachineID']}号设备,{form3_infos['MoID']} -- 最终数据存储错误,{e}")
                        flash_result = f"最终数据存储错误!,问题描述：{e}"
                        NG_dict['NG_Site'] = f"校准第二步:数据库-MPvalidation_Data1表存储"
                        NG_dict['Reason'] = f"数据库-MPvalidation_Data1表出错"
                        return flash_result, lotID3, flag, NG_dict
                    else:
                        dict_coef_data = flash_result.loc['最终系数'].to_dict()
                        # 查询COF中StandardPartNO最大值的符合条件的数据
                        sql_select = f"SELECT max(StandardPartNO) FROM COF WHERE MoID='{form3_infos['MoID']}' and CF='最终系数' and " \
                                     f"MachineID='{form3_infos['MachineID']}'"
                        row = cursor1.execute(sql_select).fetchall()
                        if row[0][0]:
                            No = int(row[0][0]) + 1
                        else:
                            No = 1
                        sql_insert = f"INSERT INTO COF (MoID,CF,ITEM_CODE,StandardPartNO," \
                                     f"dU,dx,dy,dRa,dR9,dlm,dI,MachineID,Updatetime,LotID)" \
                                     f"values ('{form3_infos['MoID']}','最终系数','{item_code}','{No}'," \
                                     f"{dict_coef_data['dU']},{dict_coef_data['dx']},{dict_coef_data['dy']}," \
                                     f"{dict_coef_data['dRa']},{dict_coef_data['dR9']},{dict_coef_data['dlm']}," \
                                     f"{dict_coef_data['dI']},'{form3_infos['MachineID']}','{datetime.now()}'," \
                                     f"'{str(form3_infos['LotID2']) + '-' + str(form3_infos['LotID3'])}')"
                        try:
                            # print(f"sql:{sql_insert}")
                            cursor1.execute(sql_insert)
                            conn.commit()
                        except Exception as e:
                            conn.rollback()
                            logger.error(f"{form3_infos['MachineID']}号设备,{form3_infos['MoID']} -- 最终系数存储错误,{e}")
                            flash_result = f"最终系数存储错误!问题描述：{e}"
                            NG_dict['NG_Site'] = f"校准第三步:数据库-COF表存储"
                            NG_dict['Reason'] = f"数据库-COF表出错"
                            return flash_result, lotID3, flag, NG_dict
                        else:
                            # 插入当前时间
                            form3_infos['UpdateTime'] = str(datetime.now())
                            # 遍历字典
                            kvs = [(k, v) for k, v in form3_infos.items() if v is not None]
                            # 创建sql语句
                            sql_data = f"INSERT INTO MPvalidation_Info1({','.join(i[0] for i in kvs)}) " \
                                       f"VALUES ({','.join(repr(i[1]) for i in kvs)});"
                            try:
                                cursor.execute(sql_data)
                                cnxn.commit()
                            except Exception as e:
                                cnxn.rollback()
                                logger.error(f"{form3_infos['MachineID']}号设备,{form3_infos['MoID']} -- 最终信息存储错误,{e}")
                                flash_result = f"最终信息存储错误!问题描述：{e}"
                                NG_dict['NG_Site'] = f"校准第三步:数据库-MPvalidation_Info1表存储"
                                NG_dict['Reason'] = f"数据库-MPvalidation_Info1表出错"
                                return flash_result, lotID3, flag, NG_dict
                            else:
                                flag = 1
                                flash_result = flash_result.to_json(orient='records', force_ascii=False)
                                logger.info(
                                    f"{form3_infos['MachineID']}号,{form3_infos['MoID']}最终系数计算成功")
                                cnxn.close()
                                conn.close()
                                return flash_result, lotID3, flag, NG_dict
                # return result.to_html(header="true") + f'<br><a href="{url_for("coef")}">返回</a>'
            else:
                logger.error("测试数据中编码与标准件不对应！")
                flash_result = "测试数据与标准数据编码不对应"
                NG_dict['NG_Site'] = "校准第三步:验证测试数据编号"
                NG_dict['Reason'] = f"测试数据与标准件数据编号不对应"
                # return "<p>测试数据与标准数据编码不对应</p> " \
                #        + f'<br><a href="{url_for("coef")}">返回</a>'
                return flash_result, lotID3, flag, NG_dict
        else:
            error_dict = {
                'CF2': '没有有效的CF2参数！',
                'CF3': '没有有效的CF3参数！',
                'standpart_data': '没有有效的标准件数据！',
                'test_data': '上传数据发生错误！',
            }
            msg = ''
            if CF2_pars.empty:
                msg += error_dict['CF2']
                NG_dict['NG_Site'] = "校准第三步:验证CF2参数"
                NG_dict['Reason'] += f"CF2"
            if CF3_pars.empty:
                if len(msg) >= 1:
                    msg += '&&'
                msg += error_dict['CF3']
                NG_dict['NG_Site'] = "校准第三步:验证CF3参数"
                NG_dict['Reason'] += f"CF3"
            if standpart_data.empty:
                if len(msg) >= 1:
                    msg += '&&'
                msg += error_dict['standpart_data']
                NG_dict['NG_Site'] = "校准第三步:验证标准件数据"
                NG_dict['Reason'] += f"标准件"
            if test_data.empty:
                if len(msg) >= 1:
                    msg += '&&'
                msg += error_dict['test_data']
                NG_dict['NG_Site'] = "校准第三步:验证测试数据"
                NG_dict['Reason'] += f"测试数据"
            logger.error(msg)
            flash_result = msg
            #        + f'<br><a href="{url_for("coef")}">返回</a>'
            return flash_result, lotID3, flag, NG_dict
    else:
        # print(f"校准数据\n{coef_data}")
        logger.error("未执行校准系数运算！")
        flash_result = "无校准数据，请先运算校准系数"
        # return "<p>无校准数据，请先运算校准系数</p> " \
        #        + f'<br><a href="{url_for("coef")}">返回</a>'
        return flash_result, lotID3, flag, NG_dict
